﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Windows.Forms;

namespace BADDGUI.Utils.SQLHelper
{
    public class DBHandler
    {
        #region Property

        private SqlConnection _Connection = null;
        public SqlConnection Connection
        {
            get { return _Connection; }
            set { _Connection = value; }
        }

        #endregion

        #region Constructor

        public DBHandler() 
        {
            this.Connection = new SqlConnection(BADDGUI.Properties.Settings.Default.BASELOG_DATA);
        }

        public DBHandler(string ConnectionString)
        {
            this.Connection = new SqlConnection(ConnectionString);
        }

        public DBHandler(SqlConnection Connection)
        {
            this.Connection = Connection;
        }

        #endregion

        public DataTable GetAViewSchema(string ViewName)
        {
            DataTable dtSchema = new DataTable();
            SqlCommand cmd = GetACommand("SELECT TOP 1 * FROM " + ViewName);
            cmd.Connection.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            dtSchema = reader.GetSchemaTable();
            cmd.Connection.Close();
            return dtSchema;
        }

        public DataTable GetAView(string ViewName, params string[] SortBy)
        {
            string strSortCol = "";
            foreach (string strCol in SortBy)
            {
                strSortCol += strSortCol == "" ? strCol : ", " + strCol;
            }
            return GetADataTable("SELECT * FROM " + ViewName + " ORDER BY " + strSortCol);
        }

        public DataTable GetAView(string ViewName,int NoOfRows,params string[] SortBy)
        {
            string strSortCol = "";
            foreach (string strCol in SortBy)
            {
                strSortCol += strSortCol == "" ? strCol : ", " + strCol;  
            }
            return GetADataTable("SELECT TOP " + NoOfRows.ToString() + " * FROM " + ViewName + " ORDER BY " + strSortCol);
        }


        public DataTable GetADataTable(string CommandText)
        {
            return GetADataTable(CommandText, new List<object>() { });
        }

        public DataTable GetADataTable(string CommandText, params object[] Parameters)
        {
            return GetADataTable(CommandText, Parameters.ToList());
        }

        public DataTable GetADataTable(string CommandText, List<object> Parameters)
        {
            DataTable dt = new DataTable();
            new SqlDataAdapter(GetACommand(CommandText, Parameters)).Fill(dt);
            return dt;
        }


        public object[] GetARow(string CommandText)
        {
            return getARow(CommandText, new List<object>() { });
        }

        public object[] GetARow(string CommandText, params object[] Parameters)
        {
            return getARow(CommandText, Parameters.ToList());
        }

        public object[] getARow(string CommandText, List<object> Parameters)
        {
            SqlDataReader dr = GetACommand(CommandText, Parameters).ExecuteReader();
            if (!dr.HasRows) return null;
            Connection.Open();
            object[] Result = new object[dr.Depth];
            int i = 0;
            while (dr.Read())
            {
                Result[i] = dr[i];
                i++;
            }
            Connection.Close();

            return Result;
        }


        public object GetAVal(string CommandText)
        {
            return GetAVal(CommandText, new List<object>() { });
        }

        public object GetAVal(string CommandText, params object[] Parameters)
        {
            return GetAVal(CommandText, Parameters.ToList());
        }

        public object GetAVal(string CommandText, List<object> Parameters)
        {
            object value;
            Connection.Open();
            value = GetACommand(CommandText, Parameters).ExecuteScalar();
            Connection.Close();

            return value;
        }


        public bool ExecSql(string CommandText)
        {
            return ExecSql(CommandText, new List<object> { });
        }

        public bool ExecSql(string CommandText, params object[] Parameters)
        {
            return ExecSql(CommandText, Parameters.ToList());
        }

        public bool ExecSql(string CommandText, List<object> Parameters)
        {
            return (GetACommand(CommandText, Parameters).ExecuteNonQuery() == 0);
        }


        public SqlCommand GetACommand(string CommandText)
        {
            return GetACommand(CommandText, new List<object>() { });
        }

        public SqlCommand GetACommand(string CommandText, params object[] Parameters)
        {
            return GetACommand(CommandText, Parameters.ToList());
        }

        public SqlCommand GetACommand(string CommandText, List<object> Parameters)
        {
            try
            {
                SqlCommand cmd = this.Connection.CreateCommand();
                cmd.CommandTimeout = 0;

                if (Regex.IsMatch(CommandText, "@s"))
                {
                    dynamic mcParams = Regex.Matches(CommandText, "@s");
                    if (mcParams.Count != Parameters.Count)
                        throw new InvalidOperationException();

                    for (int i = 0; i <= Parameters.Count - 1; i++)
                    {
                        Match mcParam = Regex.Match(CommandText, "@s");
                        CommandText = CommandText.Remove(mcParam.Index, 2).Insert(mcParam.Index, "@Param" + i.ToString());
                        cmd.Parameters.AddWithValue("@Param" + i.ToString(), Parameters[i]);
                    }
                }
                cmd.CommandText = CommandText;
                return cmd;
            }
            catch (Exception e)
            {
                ShowMsgException(e);
                throw e;
            }
        }

        public void ShowMsgException(Exception e)
        {
            MessageBox.Show(e.Message + "\\n" + e.StackTrace, e.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
            return;
        }

        public bool isValidConnection(SqlConnection connection)
        {
            try
            {
                connection.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }
    }

    public class TableHandler
    {
        DBHandler dbHandler = new DBHandler();

        #region Constructor

        public TableHandler() { }

        public TableHandler(SqlConnection Connection) { }

        public TableHandler(SqlConnection Connection, string TableName)
        {
            dbHandler.Connection = this.Connection;
        }

        #endregion

        #region Property

        private SqlConnection _Connection = null;
        public SqlConnection Connection
        {
            get { return _Connection; }
            set
            {
                _Connection = value;
                dbHandler.Connection = _Connection;
            }
        }

        private string _TableName = "";
        public string TableName
        {
            get { return _TableName; }
            set { _TableName = value; }
        }

        #endregion

        //public object getDetail(string IdentitiyColumnName,int Id,out object DAL)
        //{
        //    object[] myDetail = dbHandler.GetARow("SELECT * FROM "+ TableName +" WHERE "+ IdentitiyColumnName +" = @s", Id);

        //    //return new WardDAL(Convert.ToInt32(myWard[0]), Convert.ToString(myWard[1]),
        //    //                   Convert.ToString(myWard[2]), Convert.ToInt32(myWard[3]),
        //    //                   Convert.ToInt32(myWard[4]));
        //}
        
        public int SaveTable(string TableName, object DAL)
        {
            Dictionary<string, object> myRow = new Dictionary<string, object>();
            foreach (PropertyInfo myProperty in DAL.GetType().GetProperties())
            {
                myRow[myProperty.Name] = myProperty.GetValue(DAL, null);
            }

            string str1 = "(", str2 = " VALUES(";
            List<object> rangeargs = new List<object>();
            for (int i = 0; i < myRow.Count; i++)
            {
                str1 += myRow.Keys.ToList()[i] + (i == myRow.Count - 1 ? ")" : ",");
                str2 += "@s" + (i == myRow.Count - 1 ? ")" : ",");
                rangeargs.Add(myRow.Values.ToList()[i]);
            }
            string CommandText = "INSERT INTO " + TableName + str1 + str2 + "\n" + "SELECT @SCOPE_IDENTITY";
            return (int)dbHandler.GetAVal(CommandText, rangeargs);
        }


        public bool IsSaveValid(string ColumnName, object Value)
        {
            int count = (int)dbHandler.GetAVal(" SELECT COUNT(*) FROM [" + _TableName + "] WHERE (" + ColumnName + " = @s) ", Value);
            return count > 0 ? false : true;
        }

        public bool IsUpdateValid(string ColumnName, object Value, string IdentityColumnName, int Id)
        {
            int count = (int)dbHandler.GetAVal(" SELECT COUNT(*) FROM [" + _TableName + "] WHERE (" + ColumnName + " = @s) And (" + IdentityColumnName + " != @s) ", Value, Id);
            return count > 0 ? false : true;
        }

        public bool IsDeleteValid(string[] TableNames, string ColumnName, object Value)
        {
            int count = 0;
            foreach (string TableName in TableNames)
            {
                count += (int)dbHandler.GetAVal("SELECT COUNT(*) FROM " + TableName + " WHERE " + ColumnName + " = @s", Value);
            }
            return count > 0 ? false : true;
        }
    }

    public class DBSchema
    {
        DBHandler dbHandler;

        public DBSchema(string ConnectionString)
        {
            dbHandler = new DBHandler(ConnectionString);
        }

        public string DatabaseName
        {
            get
            {
                return dbHandler.Connection.Database;
            }
        }
        
        public ColumnCollection getColDef(string ObjectName)
        {
            Server myServer = new Server(new ServerConnection(dbHandler.Connection));
            Table myTable = myServer.Databases[dbHandler.Connection.Database].Tables[ObjectName];
            return myTable.Columns;
        }
    }

    public class SPHandler
    {
        private string[] ExcludeColumns = new string[] {"TrxNo", "CreateDateTime", "UpdateDateTime" };

        string connectionstring = "";
        public SPHandler(string ConnectionString)
        {
            connectionstring = ConnectionString;
        }

        public string getStdViewQuery(string TableName,QueryType QType)
        {
            DBSchema myDbSchema = new DBSchema(connectionstring);
            string query = "";

            query += QType.ToString() + " VIEW vw_" + TableName + 
                                        "\nAS\n" + 
                                        "SELECT ";
                                        
            ColumnCollection myColumns = myDbSchema.getColDef(TableName);
            int i = 0;
            foreach (Column myCol in myColumns)
			{
                i++;
                query += "\n\t" + myCol.Name + " AS " + '[' + Regex.Replace(myCol.Name, "([a-z])([A-Z])", "$1 $2") + ']' + (i == myColumns.Count ? "" : ",");
			}

            query += "\nFROM " + TableName;

            return query;
        }

        public string getUserDefinedView(string TableName,QueryType QType)
        {
            string query = "";
            
            return query;
        }

        public string getXMLSchemaQuery()
        {
            return "";
        }

        public string getStdProcQuery(string TableName,QueryType QType, ProcType PType)
        {
            string query = "";

            switch (PType)
	        {
                case ProcType.INSERT: query = getInsertSQL(TableName, QType); break;
                case ProcType.UPDATE: query = getUpdateSQL(TableName, QType); break;
                case ProcType.DELETE: query = getDeleteSQL(TableName, QType); break;
	        }

            query += "\n\nIF @@Error <> 0\n\tRETURN -1\nELSE\n\tRETURN 0";
            return query;
        }


        protected internal string getDeleteSQL(string TableName, QueryType QType)
        {
            string SQL = "";
            string strParam1 = "";
            string strCondition = "";

            DBSchema myDbSchema = new DBSchema(connectionstring);
            ColumnCollection myColumns = myDbSchema.getColDef(TableName);
            foreach (Column myCol in myColumns)
            {
                if (!ExcludeColumns.Contains(myCol.Name))
                {
                    if (myCol.InPrimaryKey)
                    {
                        strParam1 += (strParam1 == "" ? "" : ",") + getParamString(myCol);
                        strCondition += getCondition(myCol);
                    }
                }
            }
            strParam1 += ",\n\t@UpdateBy " + myColumns["UpdateBy"].DataType.SqlDataType + "[" + myColumns["UpdateBy"].DataType.MaximumLength.ToString() + "],\n\t" +
                                "@Type Int = Null";
            SQL += QType.ToString() + " PROCEDURE spd_" + TableName +
                        strParam1 + "\nAS\n\n" +
                     "UPDATE " + TableName +
                     "\nSET StatusCode = CASE WHEN @Type = 1 THEN 'USE' ELSE 'DEL' END," +
                        "\n\tUpdateBy=@UpdateBy," +
                        "\n\tUpdateDateTime=GetDate()" +
                     "\nWHERE " + strCondition;

            return SQL;
        }

        protected internal string getUpdateSQL(string TableName, QueryType QType)
        {
            string SQL = "";
            string strParam1 = "";
            string strDefault = "";
            string strColumns = "";
            string strCondition = "";

            DBSchema myDbSchema = new DBSchema(connectionstring);
            ColumnCollection myColumns = myDbSchema.getColDef(TableName);
            foreach (Column myCol in myColumns)
            {
                if (!ExcludeColumns.Contains(myCol.Name))
                {
                    strParam1 += (strParam1 == "" ? "" : ",") + getParamString(myCol);
                    strDefault += getParamDefaultString(myCol);
                    strColumns += (strColumns == "" ? "" : ",\n\t") + myCol.Name + "=@" + myCol.Name;
                    if (myCol.InPrimaryKey) strCondition += getCondition(myCol);
                }
            }
            SQL += QType.ToString() + " PROCEDURE spu_" + TableName +
                        strParam1 + "\nAS\n" +
                     strDefault + "\n\n" +
                     "UPDATE " + TableName + "\n" +
                     "SET " + strColumns + "\n" +
                     "WHERE " + strCondition;

            return SQL;
        }

        protected internal string getInsertSQL(string TableName, QueryType QType)
        {
            string SQL = "";
            string strParam1 = "";
            string strParam2 = "";
            string strColumns = "";
            string strDefault = "";

            DBSchema myDbSchema = new DBSchema(connectionstring);
            ColumnCollection myColumns = myDbSchema.getColDef(TableName);
            foreach (Column myCol in myColumns)
            {
                if (!ExcludeColumns.Contains(myCol.Name))
                {
                    strParam1 += (strParam1 == "" ? "" : ",") + getParamString(myCol);
                    strDefault += getParamDefaultString(myCol);
                    strColumns += (strColumns == "" ? "" : ",\n\t") + myCol.Name;
                    strParam2 += (strParam2 == "" ? "@" : ",\n\t@") + myCol.Name;
                }
            }
            SQL += QType.ToString() + " PROCEDURE spi_" + TableName +
                            strParam1 + "\nAS\n" +
                     strDefault +
                     "\n\nINSERT INTO " + TableName + "\n\t" +
                     "(" + strColumns + ")" + "\n" +
                     "VALUES (" + strParam2 + ")";

            return SQL;
        }

        protected internal string getCondition(Column col)
        {
            string result = "";
            result += (result == "" ? "" : " AND\n\t") + col.Name + "=@" + col.Name;
            return result;
        }

        protected internal string getParamString(Column col)
        {
            string result = "";
            string myColName = col.Name;
            DataType myDataType = col.DataType;

            if (myDataType.SqlDataType == SqlDataType.DateTime ||
                myDataType.SqlDataType == SqlDataType.Xml ||
                myDataType.SqlDataType == SqlDataType.Image ||
                myDataType.SqlDataType == SqlDataType.Int ||
                myDataType.SqlDataType == SqlDataType.NText)
            {
                result += "\n\t@" + myColName + " " + myDataType.SqlDataType.ToString();
            }
            else
            {
                result += "\n\t@" + myColName + " " + myDataType.SqlDataType.ToString() + "(" + myDataType.MaximumLength + ")";
            }

            return result;
        }

        protected internal string getParamDefaultString(Column col)
        {
            string result = "";

            switch (col.DataType.SqlDataType)
            {
                case SqlDataType.Char:
                case SqlDataType.VarChar:
                case SqlDataType.NChar:
                case SqlDataType.NVarChar:
                case SqlDataType.NText:
                case SqlDataType.Text:
                    result += "\nIF LEN(@" + col.Name + ") = 0" + " SELECT @" + col.Name + " = NULL";
                    break;
                case SqlDataType.Date:
                case SqlDataType.DateTime:
                    result += "\nIf @" + col.Name + " = {d '1899-12-31'} Select @" + col.Name + " = Null";
                    break;
                default:
                    result += "\nIF LEN(@" + col.Name + ") = 0";
                    break;
            }

            return result;
        }
    }
}